package com.mes.handler.plan;

import com.basis.utils.HibernateDao;
import org.codehaus.jettison.json.JSONArray;
import org.codehaus.jettison.json.JSONException;
import org.codehaus.jettison.json.JSONObject;
import org.hibernate.Query;
import org.hibernate.transform.Transformers;
import org.openbravo.client.kernel.BaseActionHandler;
import org.openbravo.dal.core.OBContext;
import org.openbravo.dal.service.OBDal;

import java.util.List;
import java.util.Map;

/**
 * 作业订单强制完工
 */
public class SalesOrdersHandler extends BaseActionHandler {
	@Override
	protected JSONObject execute(Map<String, Object> parameters, String data) {
		JSONObject jsonRep = new JSONObject();
				try {
			//设置最高权限
			OBContext.setAdminMode();
			JSONObject reqData = new JSONObject(data);
            //获取销售订单信息
            String sql = "SELECT " +
					"CASE " +
					"WHEN " +
					" t1.STATUS = 'AUDIT' THEN " +
					" '已审核'  " +
					" WHEN t1.STATUS = 'CREATE' THEN " +
					" '创建' ELSE 'other'  " +
					" END STATUS, " +
					" nvl ( t1.emes_customer_id, '客户未填写收件人名称' ) customer, " +
					" t1.created, " +
					" NVL ( t1.phone, 'NONE' ) phone, " +
					" NVL ( t1.address, 'NONE' ) address, " +
					" NVL ( t1.CODE, 'NONE' ) CODE, " +
					" NVL ( t2.qty, 0 ) qty, " +
					" ( SELECT P.m_product_id FROM m_product P WHERE P.m_product_id = t2.m_product_id ) productid, " +
					" ( SELECT P.NAME FROM m_product P WHERE P.m_product_id = t2.m_product_id ) product, " +
					" ( SELECT NAME FROM c_uom WHERE c_uom_id = t2.c_uom_id ) uom, " +
					" ( SELECT P.imageurl FROM m_product P WHERE P.m_product_id = t2.m_product_id ) img  " +
					"FROM " +
					" wms_sales_order t1, " +
					" wms_sales_order_detail t2  " +
					"WHERE " +
					" t1.wms_sales_order_id = t2.wms_sales_order_id";

			if(reqData.get("type").toString().equalsIgnoreCase("SEARCH")) {
			    String name = (String) reqData.get("name");
			    if(!"NONE".equalsIgnoreCase(name)){
                    sql+=" and t1.emes_customer_id like '%" + name + "%' ";
			    }
			    String code = (String) reqData.get("code");
                if(!"NONE".equalsIgnoreCase(code)){
                    sql+=" and t1.code like '%" + code + "%' ";
                }
                String product = (String) reqData.get("product");
                if(!"NONE".equalsIgnoreCase(product)){
                    sql+=" and t2.m_product_id like '%" + product + "%' ";
                }
                String start_time = (String) reqData.get("start_time");
                if(!"NONE".equalsIgnoreCase(start_time)){
                    sql+=" and t1.created > timestamp'" + start_time.replace("：",":") + "'" ;
                }
                String end_time = (String) reqData.get("end_time");
                if(!"NONE".equalsIgnoreCase(end_time)){
                    sql+=" and t1.created < timestamp'" + end_time.replace("：",":")+"'";
                }
            }
			Long count = getSOCounts();
            System.out.println(sql);
			Query query = OBDal.getInstance().getSession().createSQLQuery(sql);
			query.setFirstResult(0);
			query.setFetchSize(1);
			List<Map> ordersInfo = query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();

			jsonRep.put("result",true);
			jsonRep.put("count",count);
			jsonRep.put("data",getRepData(ordersInfo));
			return jsonRep;
		} catch (Exception e) {
			try {
				jsonRep.put("result",false);
				jsonRep.put("result","系统繁忙，请稍后再试。");
			} catch (JSONException ex) {
				ex.printStackTrace();
			}
			e.printStackTrace();
			OBDal.getInstance().rollbackAndClose();
			return jsonRep;
		}finally{
			OBContext.restorePreviousMode();
		}
	}

	//获取所有
	public Long getSOCounts(){
		String sql = "select count(*) from wms_sales_order";
		return HibernateDao.getSqlCount(sql);
	}

	public JSONObject getRepData(List<Map> ordersInfo) throws JSONException {
		JSONObject repData = new JSONObject();
		//定义一级子元素
		JSONObject child1Obj;
		//定义二级子元素
		JSONArray child2Obj;
		//定义三级子元素
		JSONObject child3Obj;
		for(Map obj:ordersInfo){
			//如果存在，则取存在的值。
			child3Obj = new JSONObject();
			if(repData != null && repData.has((String) obj.get("code")))
			{
				child1Obj = (JSONObject) repData.get(obj.get("code").toString());
				child2Obj = child1Obj.getJSONArray("details");
				child3Obj.put("productid",obj.containsKey("productid") ? obj.get("productid"):"NONE");
				child3Obj.put("product",obj.containsKey("product") ? obj.get("product"):"NONE");
				child3Obj.put("qty",obj.containsKey("qty") ? obj.get("qty") : "NONE");
				child3Obj.put("img",obj.containsKey("img") ? obj.get("img") : "NONE");
				child3Obj.put("uom",obj.containsKey("uom") ? obj.get("uom") : "NONE");
				child2Obj.put(child3Obj);
				child1Obj.put("details",child2Obj);
			}else{
				child1Obj = new JSONObject();
				child2Obj = new JSONArray();
				child1Obj.put("salesorder",(String)obj.get("code"));

				child1Obj.put("address",obj.containsKey("address") ? obj.get("address") : "NONE");
				child1Obj.put("customer",obj.containsKey("customer") ? obj.get("customer") : "NONE");
				child1Obj.put("status",obj.containsKey("status") ? obj.get("status"): "NONE");
				child1Obj.put("orderdate",obj.containsKey("created") ? obj.get("created") : "NONE");
				child1Obj.put("phone",obj.containsKey("phone") ? obj.get("phone") : "NONE");

				child3Obj.put("productid",obj.containsKey("productid") ? obj.get("productid"):"NONE");
				child3Obj.put("product",obj.containsKey("product") ? obj.get("product"):"NONE");
				child3Obj.put("qty",obj.containsKey("qty") ? obj.get("qty") : "NONE");
				child3Obj.put("img",obj.containsKey("img") ? obj.get("img") : "NONE");
				child3Obj.put("uom",obj.containsKey("uom") ? obj.get("uom") : "NONE");
				child2Obj.put(child3Obj);
				child1Obj.put("details",child2Obj);
				repData.put((String)obj.get("code"),child1Obj);
			}
		}
		return repData;
	}
}
